PHYSICAL DATA ORGANIZATION FUNDAMENTALS. SPACE SEARCH ALGORITHM PART-2


In this article I am considering the space search algorithm for  Simple and Partitioned table spaces.

First let us consider a table space with no Index.

When DB2 tries to insert a row into a table space, DB2 tries to look for space in the same page where it's cursor is positioned. In other words DB2 tries to insert the row into which ever page on which it's current pointer is positioned. This saves DB2 a lot of effort in finding an other page as it does not matter as to what page the row is inserted into due to the lack of an index.

If space is not available on the candidate page search the end of the table space

If space is not available at the end of the table space search every single space map page in the table space or partition  for available space.

If space not available extend the VSAM dataset.






Next let us consider a table space with a clustering Index. If no explicit clustering index is defined the first index defined on the table is the default clustering index.

When DB2 tries to insert a row into a table space, DB2 tries to look for space in the candidate page. The candidate page is determined by the page that holds the reocords that have a key value immediately lesser and immediately greater than the key value of the row being inserted. Let us assume that we are inserting a row with a key value (PHONE NUMBER) of 210-341-6091. There are already two rows in the table with key values of 210-341-6090 and 210-34-6092 and both rows reside on page 42 (say) then Page 42 is the Candidate page. This page is the ideal location for the new row.

If space is not available on the candidate page search the space map page controlling the range of the candidate page for 16 pages forward. In other words, if space is not available in page 42 then search the space map page that belongs to this range for space avaialbility  in pages 43 through  58.

If space not found search the space map page for available space 16 pages backwards. Pages 41 through 26.

If space not found search the tail end of the table space for any available space.

If space not found search every single space map page in the partition or table space for available space.

If space not found , extend VSAM dataset.